package nl.hva.web.mvcwebapp.doa;

import java.util.ArrayList;
import java.util.List;
import nl.hva.web.mvcwebapp.models.Page;
import nl.hva.web.mvcwebapp.models.PageWidget;
import nl.hva.web.mvcwebapp.models.Widget;
import nl.hva.web.mvcwebapp.service.HibernateUtil;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.transform.Transformers;

public class PageDAO {

    public PageDAO() {
    }
    /*
     * Page Related Methods
     */

    public static int getWidgetLocationId(int pageID, int widgetID) {
        List<PageWidget> pwlist = getAllPageWidgets();
        for (int i = 0; i < pwlist.size(); i++) {
            if (pwlist.get(i).getPage().getPageId() == pageID && pwlist.get(i).getWidget().getWidgetId() == widgetID) {
                return pwlist.get(i).getWidgetLocation();
            }
        }
        return -1;
    }

    public static void insertPageWidgetRelation(int pageID, int widgetID, int locationID) {
        Page pagetastic = new Page();
        Widget widgetastic = new Widget();
        PageWidget pw = new PageWidget();
        try {
            pagetastic = getPage(pageID);
            widgetastic = getWidget(widgetID);


            pw.setPage(pagetastic);
            pw.setWidget(widgetastic);
            pw.setWidgetLocation(locationID);
        } catch (Exception e) {
            System.out.println("Invalid values received to save widget,page,location.");
        }

        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction tx = session.beginTransaction();
        try {
            if (pw != new PageWidget()) {
                session.save(pw);
                tx.commit();
            }
        } catch (Exception e) {
            System.out.println("Cannot save widget: " + widgetID + ", on page: " + pageID + ", at location: " + locationID);
            tx.rollback();
        }
    }

    public static List<Page> getAllPages() {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction tx = session.beginTransaction();
        List<Page> page_list = session.createCriteria(Page.class).list();
        tx.commit();
        return page_list;
    }

    public static Page getPage(int page_id) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction tx = session.beginTransaction();
        Page page = (Page) session.get(Page.class, page_id);
        tx.commit();
        return page;

    }

    public static Page getPageByPath(String page_path) {
        Page returnPage = null;
        List<Page> pages = getAllPages();
        for (int i = 0; i < pages.size(); i++) {
            Page tempPage = pages.get(i);

            if (tempPage.getPagePath().equals(page_path)) {
                return tempPage;

            }
        }
        return returnPage;
    }

    public static ArrayList insertPage(Page page) {
        ArrayList validation_errors = new ArrayList();
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction tx = session.beginTransaction();
        try {
            session.save(page);
            tx.commit();
        } catch (Exception e) {
        }
        return validation_errors;
    }

    public static ArrayList updatePage(Page page) {
        ArrayList validation_errors = new ArrayList();
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction tx = session.beginTransaction();
        session.update(page);
        tx.commit();
        return validation_errors;
    }

    public static void deletePage(int page_id) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction tx = session.beginTransaction();
        Page page = (Page) session.load(Page.class, page_id);
        session.delete(page);
        tx.commit();
    }

    /*
     * Page_Widget Related Methods
     */
    public static List<PageWidget> getAllPageWidgets() {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction tx = session.beginTransaction();
        List<PageWidget> pagewidget_list = session.createCriteria(PageWidget.class).list();
        tx.commit();
        return pagewidget_list;
    }

    public static List<Widget> getWidgetsByPath(String path) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();

        List<Widget> widget_list = null;
        Transaction tx = null;

        try {
            tx = session.beginTransaction();
            widget_list = session.createSQLQuery(
                    "SELECT w.WIDGET_ID as widgetId, w.WIDGET_NAME as widgetName, w.WIDGET_PATH as widgetPath "
                    + "FROM pdl014.page p "
                    + "left join pdl014.page_widget pw on pw.PAGE_ID = p.PAGE_ID "
                    + "left join pdl014.widget w on w.WIDGET_ID = pw.WIDGET_ID "
                    + "where p.PAGE_PATH = '" + path + "' LIMIT 0, 1000")
                    .addScalar("widgetId")
                    .addScalar("widgetName")
                    .addScalar("widgetPath")
                    .setResultTransformer(Transformers.aliasToBean(Widget.class))
                    .list();
            tx.commit();
        } catch (HibernateException e) {
            if (tx != null) {
                tx.rollback();
            }
            e.printStackTrace();
        }
        return widget_list;
    }

    public static List<Widget> getWidgetsByPathAndLocId(String path, int locationId) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();

        List<Widget> widget_list = null;
        Transaction tx = null;

        try {
            tx = session.beginTransaction();
            widget_list = session.createSQLQuery(
                    "SELECT w.WIDGET_ID as widgetId, w.WIDGET_NAME as widgetName, w.WIDGET_PATH as widgetPath "
                    + "FROM pdl014.page p "
                    + "left join pdl014.page_widget pw on pw.PAGE_ID = p.PAGE_ID "
                    + "left join pdl014.widget w on w.WIDGET_ID = pw.WIDGET_ID "
                    + "where p.PAGE_PATH = '" + path + "' "
                    + "and pw.WIDGET_LOCATION = '" + locationId + "' LIMIT 0, 1000")
                    .addScalar("widgetId")
                    .addScalar("widgetName")
                    .addScalar("widgetPath")
                    .setResultTransformer(Transformers.aliasToBean(Widget.class))
                    .list();
            tx.commit();
        } catch (HibernateException e) {
            if (tx != null) {
                tx.rollback();
            }
            e.printStackTrace();
        }
        return widget_list;
    }

    public static ArrayList insertPageWidget(PageWidget pagewidget) {
        ArrayList validation_errors = new ArrayList();
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction tx = session.beginTransaction();
        session.save(pagewidget);
        tx.commit();
        return validation_errors;
    }

    public static ArrayList updatePageWidget(PageWidget pagewidget) {
        ArrayList validation_errors = new ArrayList();
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction tx = session.beginTransaction();
        session.update(pagewidget);
        tx.commit();
        return validation_errors;
    }

    public static void deletePageWidget(int pagewidget) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction tx = session.beginTransaction();
        PageWidget pagew = (PageWidget) session.load(PageWidget.class, pagewidget);
        session.delete(pagew);
        tx.commit();
    }

    public static void deletePageWidget(PageWidget pw) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction tx = session.beginTransaction();
        session.delete(pw);
        tx.commit();
    }

    public static void deletePageWidgetOnPage(int page) {
        Page pagetastic = getPage(page);
        Session session = null;
        try {
            //deleting Entries
            String hql = "DELETE FROM PageWidget WHERE PAGE_ID = '" + page + "'";
            session = HibernateUtil.getSessionFactory().getCurrentSession();
            session.beginTransaction();
            int row = session.createQuery(hql).executeUpdate();
            session.getTransaction().commit();
            // making sure all 'relations' are cleared
            pagetastic.setPageWidget(null);
            updatePage(pagetastic);

            if (row == 0) {
                System.out.println("Did not delete any rows!");
            } else {
                System.out.println("Deleted rows: " + row);
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

    /*
     * Widget Related Methods
     */
    public static List<Widget> getAllWidgets() {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction tx = session.beginTransaction();
        List<Widget> widget_list = session.createCriteria(Widget.class).list();
        tx.commit();
        return widget_list;
    }

    public static Widget getWidget(int widget_id) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction tx = session.beginTransaction();
        Widget widget = (Widget) session.load(Widget.class, widget_id);
        tx.commit();
        return widget;
    }

    public static ArrayList insertWidget(Widget widget) {
        ArrayList validation_errors = new ArrayList();
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction tx = session.beginTransaction();
        session.save(widget);
        tx.commit();
        return validation_errors;
    }

    public static ArrayList updateWidget(Widget widget) {
        ArrayList validation_errors = new ArrayList();
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction tx = session.beginTransaction();
        session.update(widget);
        tx.commit();
        return validation_errors;
    }

    public static void deleteWidget(int widget_id) {
        Session session = HibernateUtil.getSessionFactory().getCurrentSession();
        Transaction tx = session.beginTransaction();
        Page page = (Page) session.load(Widget.class, widget_id);
        session.delete(page);

        tx.commit();
    }

    public static void autoCreateDefaults() {
        //Widgets
        List<Widget> widgetList = null;
        widgetList.add(new Widget("Popular Courses", "/WEB-INF/widgets/popular-courses.jsp"));
        widgetList.add(new Widget("Header Menu", "/WEB-INF/widgets/top-navigation.jsp"));
        widgetList.add(new Widget("Latest News", "/WEB-INF/widgets/latest-news.jsp"));
        widgetList.add(new Widget("Copyright Footer", "/WEB-INF/templates/footer.jsp"));
        widgetList.add(new Widget("Banner", "/WEB-INF/widgets/banner.jsp"));
        widgetList.add(new Widget("Three Bottom", "/WEB-INF/widgets/three-blocks.jsp"));
        for (int i = 0; i < widgetList.size(); i++) {
            insertWidget(widgetList.get(i));
        }
        //Pages
        List<Page> pageList = null;

        for (int i = 0; i < pageList.size(); i++) {
            insertPage(pageList.get(i));
        }
    }
}
